using System;
using System.Collections.Generic;
using System.Text;
using System.Transactions;

namespace SubSonic {
    public abstract class Migration {
        
        public const string SCHEMA_INFO = "SubSonicSchemaInfo";

        public static int GetCurrentVersion(string providerName) {
            
            int currentVersion=0;
            //see if table exists
            try {
                Query q = new Query(SCHEMA_INFO, providerName);
                currentVersion = (int)q.ExecuteScalar();
            } catch {
                //this means the table doesn't exist
                //or that it doesn't have a record in it

                //try and drop, to be safe
                if(!DataService.TableExists(providerName,SCHEMA_INFO)){
                    CreateSchemaInfo(providerName);
                }

                //delete all rows
                Query q2=new Query(SCHEMA_INFO,providerName);
                q2.QueryType = QueryType.Delete;
                q2.Execute();

                //now add one row
                QueryCommand cmd = new QueryCommand("INSERT INTO " + SCHEMA_INFO + "(version) VALUES(0)",providerName);
                DataService.ExecuteQuery(cmd);

            }

            return currentVersion;
            
        }

        static void CreateSchemaInfo(string providerName) {
            TableSchema.Table tbl = new TableSchema.Table(providerName, SCHEMA_INFO);
            tbl.AddColumn("version", System.Data.DbType.Int32,0,false,"0");
            DataService.CreateTable(providerName, tbl);
        }
        public static void IncrementVersion(string providerName) {
            Query q=new Query(providerName,SCHEMA_INFO);
            q.QueryType=QueryType.Update;
            q.AddUpdateSetting("version", "version+1");
            q.Execute();
        }
        public static void DecrementVersion(string providerName) {
            Query q = new Query(providerName, SCHEMA_INFO);
            q.QueryType = QueryType.Update;
            q.AddUpdateSetting("version", "version-1");
            q.Execute();
        }
        protected void Create(string providerName, TableSchema.TableCollection tables) {
            
            //execute in a transaction for obvious reasons :)

            using (SharedDbConnectionScope sharedConnectionScope = new SharedDbConnectionScope()) {
                using (TransactionScope ts = new TransactionScope()) {
                    foreach (TableSchema.Table tbl in tables) {

                        //need to make sure this table has a pk defined
                        //if not, add one
                        //we'll do yer job for ya :)
                        if (tbl.PrimaryKey == null)
                            tbl.AddPrimaryKeyColumn();

                        DataService.CreateTable(providerName, tbl);
                    }
                    ts.Complete();
                }
            }
        }

        protected void AddSubSonicStateColumns(TableSchema.Table table) {

            table.AddColumn(ReservedColumnName.CREATED_ON, System.Data.DbType.DateTime, 0, false, "getdate()");
            table.AddColumn(ReservedColumnName.MODIFIED_ON, System.Data.DbType.DateTime, 0, false, "getdate()");            
            table.AddColumn(ReservedColumnName.CREATED_BY, System.Data.DbType.String);
            table.AddColumn(ReservedColumnName.MODIFIED_BY, System.Data.DbType.String);
            

        }

        protected void Create(TableSchema.TableCollection tables) {
            Create("", tables);
        }

        protected void AlterColumn(string providerName, string tableName, TableSchema.TableColumn column) {
            if (column.Table == null)
                column.Table = DataService.GetSchema(tableName, providerName);
            DataService.AlterColumn(providerName,column);
        }
        protected void AlterColumn(string tableName, TableSchema.TableColumn column) {
            
            AlterColumn("", tableName, column);
        }
        protected void AlterColumn(TableSchema.TableColumn column) {
            if (column.Table == null)
                throw new Exception("The table schema isn't defined for this table - you need to specify the provider and table name");
            AlterColumn(column.Table.Provider.Name, column.Table.Name, column);
        }

        protected void RemoveColumn(string providerName, string tableName, string columnName) {
            DataService.RemoveColumn(providerName, tableName, columnName);
        }
        protected void RemoveColumn(string tableName, string columnName) {
            RemoveColumn("", tableName, columnName);
        }

        protected void AddColumn(string providerName, string tableName, TableSchema.TableColumn column) {
            DataService.AddColumn(providerName, tableName, column);
        }
        protected void AddColumn(string tableName, TableSchema.TableColumn column) {
            AddColumn("", tableName, column);
        }

        protected void Drop(string providerName, string tablename) {
            DataService.DropTable(providerName, tablename);
        }
        protected void Drop(string tablename) {
            Drop("", tablename);
        }
        protected void Drop(string providerName, string[] tables) {
            using (SharedDbConnectionScope sharedConnectionScope = new SharedDbConnectionScope()) {
                using (TransactionScope ts = new TransactionScope()) {
                    foreach (string tablename in tables) {
                        DataService.DropTable(providerName, tablename);
                    }
                }
            }

        }
        protected void Drop(string[] tables) {
            Drop("", tables);

        }
        public abstract void Up();
        public abstract void Down();

    }
}
